一、第六堂情境練習：下半年客戶別訂單數量統計

(一) 說明

分別使用 純JOIN、IN、EXISTS 三種寫法，統計1996-07-01 ~ 1996-12-31 的所有訂單當中，依照客戶別，統曾經下訂過的客戶分別訂了幾筆訂單

列出欄位：

• 客戶姓名(Customers資料表的CustomerName)
• 客戶下半年下訂的訂單數量

(二) 實作結果

1.JOIN

SELECT Customers.CustomerName,
	   Order_Count_Result.Order_Count
FROM Customers   
INNER JOIN (
            SELECT CustomerID,
                   COUNT(OrderID) AS Order_Count
            FROM Orders
            WHERE OrderDate >= '1996-07-01' AND
                  OrderDate <= '1996-12-31'
            GROUP BY CustomerID
    	 ) AS Order_Count_Result
ON Order_Count_Result.CustomerID = Customers.CustomerID

2.WHERE IN

SELECT  Customers.CustomerName,
		( SELECT  COUNT(OrderID) AS Order_Count
		  FROM Orders
          WHERE CustomerID = Customers.CustomerID AND
                OrderDate >= '1996-07-01' AND
                OrderDate <= '1996-12-31'
          GROUP BY CustomerID
        ) AS Order_Count
FROM Customers
WHERE Customers.CustomerID IN ( SELECT CustomerID
                                FROM Orders
                                WHERE OrderDate >= '1996-07-01' AND
                				OrderDate <= '1996-12-31')

3.WHERE EXISTS

SELECT  Customers.CustomerName,
		( SELECT  COUNT(OrderID) AS Order_Count
		  FROM Orders
          WHERE CustomerID = Customers.CustomerID AND
                OrderDate >= '1996-07-01' AND
                OrderDate <= '1996-12-31'
          GROUP BY CustomerID
        ) AS Order_Count
FROM Customers
WHERE EXISTS ( SELECT CustomerID
               FROM Orders
               WHERE Orders.CustomerID = Customers.CustomerID AND
               		 OrderDate >= '1996-07-01' AND
               		 OrderDate <= '1996-12-31')

二、CREATE TABLE 語法結構

CREATE TABLE [IF NOT EXISTS] table_name(
 -- 設定欄位
 主鍵欄位名稱 欄位資料型態 [NOT NULL] [IDENTITY (從多少開始, 每次累加多少)] [UNIQUE] ,
 其他欄位名稱 欄位資料型態 [NOT NULL] [IDENTITY (從多少開始, 每次累加多少)] [UNIQUE] ,
 ...,
 
 -- 設定欄位限制：外來鍵與索引鍵
 [PRIMARY KEY (套用得主鍵欄位) ],
 [CONSTRAINT 外來索引鍵的名稱 FOREIGN KEY (關聯的來源表名稱) REFERENCES Persons(關聯的來源欄位)],
 [UNIQUE INDEX `唯一鍵的索引名稱` (`索引的欄位`)]
);

三、建立家事管理的四張資料表的結構

DB Diagram

(一) Family 家庭成員資料表

CREATE TABLE `Family` (
	`FamilyId` VARCHAR(100) NOT NULL,
	`FamilyName` VARCHAR(10) NULL DEFAULT NULL,
	`FamilySex` VARCHAR(10) NULL DEFAULT NULL,
	`BirthDate` DATETIME NULL DEFAULT NULL,
	`PhoneNumber` VARCHAR(20) NULL DEFAULT NULL,
	PRIMARY KEY (`FamilyId`),
	UNIQUE INDEX `PhoneNumber` (`PhoneNumber`)
    
);

(二) CleanItemList 家事項目清單

CREATE TABLE `CleanItemList` (
	`CleanItem` VARCHAR(50) NOT NULL,
	`ItemName` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`CleanItem`)
);

(三) CleanSchedule 家事排班表

CREATE TABLE `CleanSchedule` (
	`CleanScheduleId` VARCHAR(100) NOT NULL,
	`CleanDateTime` DATETIME NULL DEFAULT NULL,
	`FamilyId` VARCHAR(100) NULL DEFAULT NULL,
	`CleanItem` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`CleanScheduleId`),
	INDEX `FK_cleanschedule_family` (`FamilyId`),
	INDEX `CleanDateTime` (`CleanDateTime`),
	CONSTRAINT `FK_cleanschedule_family` FOREIGN KEY (`FamilyId`) REFERENCES `family` (`FamilyId`)
)ENGINE=InnoDB
;

(四) CleanRecord 實際做家事紀錄表

CREATE TABLE `CleanRecord` (
	`CleanRecordId` VARCHAR(100) NOT NULL,
	`CleanScheduleId` VARCHAR(100) NULL DEFAULT NULL,
	`FamilyId` VARCHAR(100) NULL DEFAULT NULL,
	`StartTime` DATETIME NULL DEFAULT NULL,
	`EndTime` DATETIME NULL DEFAULT NULL,
	PRIMARY KEY (`CleanRecordId`),
	INDEX `FK_cleanrecord_cleanschedule` (`CleanScheduleId`),
	INDEX `FK_cleanrecord_family` (`FamilyId`),
	CONSTRAINT `FK_cleanrecord_cleanschedule` FOREIGN KEY (`CleanScheduleId`) REFERENCES `CleanSchedule` (`CleanScheduleId`),
	CONSTRAINT `FK_cleanrecord_family` FOREIGN KEY (`FamilyId`) REFERENCES `Family` (`FamilyId`)
);

四、常見的欄位限制：主鍵、外來鍵、索引、唯一

回想有一次面試某家公司，其中一題的題目是：
「請描述主鍵(Primary Key)、外來鍵 (Foreign Key)、索引鍵 (Index)、唯一(Unique) 這四種欄位限制的差別，並舉例適用的情境」

(一)主鍵 (PRIMARY KEY)

1. 適用情境：用來明確識別在資料當中的每一筆資料，是不同的資料。每筆資料的主鍵不能重複
2. 主鍵常見的使用型態：

• int
• GUID/UUID

例如：每一個排班紀錄都有一個不重複的流水號(CleanScheduleId)

PRIMARY KEY (`CleanScheduleId`)

(二) 外來鍵 (FOREIGN KEY)

1. 適用情境：用來存放來別張資料表的資料主鍵，透過JOIN合併資料
2. 常見的外來鍵參考對象的刪除/更新規則：

• 連鎖反應做法 CASCADE:
  來源的資料被刪除了，有關聯來源的資料也會一併刪除
  如果是 parent FK更新了新值，則child 對應的FK會更新。
• 限制性做法 RESTRICT:
  有關聯來源的資料如果沒有被刪除，來源的資料無法刪除
• 虛值化做法 SET NULL:
  有關聯來源的資料如果沒有被刪除，來源的資料刪除後，有關聯來源的資料值將被修改為NULL

例如：每一筆實際做家事紀錄(CleanRecord) 都有在排班表(CleanScheduleId)對應的排班流水號 (CleanScheduleId)

CONSTRAINT `FK_cleanrecord_cleanschedule` FOREIGN KEY (`CleanScheduleId`) REFERENCES `CleanSchedule` (`CleanScheduleId`)

(三) 索引 (INDEX)

1. 適用情境：設定在常用來當作查詢條件的欄位
2. 目的：藉由資料庫的演算法提高查詢速度
3. 成本：占用額怪的磁碟空間

舉例：家事排班表的打掃日期，經常被拿來當作查詢統計的條件

INDEX `CleanDateTime` (`CleanDateTime`)

(四) 唯一 (UNIQUE)

適用情境：資料不能重複的欄位，可以接受 NULL 值。
例如：家庭成員的手機號碼，可以設定為UNIQUE

UNIQUE INDEX `PhoneNumber` (`PhoneNumber`)

五、延伸閱讀：索引的效能觀念

1. SQL Server Index 介紹(基本)
   https://dotblogs.com.tw/wuu1992/2017/11/04/224631

2. SQL Server 如何寫出高效能 TSQL – 關於索引不可不知道的事
   https://blogs.technet.microsoft.com/technet_taiwan/2015/01/22/tsql-3/

3. My SQL：MySQL 超新手入門（9）表格與索引
   http://www.codedata.com.tw/database/mysql-tutorial-9-table-index/

4. MySql索引原理與使用大全
   https://codertw.com/%E7%A8%8B%E5%BC%8F%E8%AA%9E%E8%A8%80/477236/

六、相關情境練習

使用資料表：Orders 訂單資料表

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_datediff

題目： Orders 的Create Table SQL

根據 Orders 裡面的資料，撰寫Orders的Create Table SQL，並且在每個欄位加入適當的欄位限制。